package com.yunqi.sql.util;

import com.yunqi.sql.entity.Column;
import lombok.Data;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author qsx
 * @date 2020-08-31 18:09:45
 */
@Data
public class JdbcUtil {

    //public static final String URL = "jdbc:mysql://192.168.3.22:22306/ykt_site?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true"; // 数据库连接的url
    //public static final String SCHEMA = "ykt_site"; // 数据库名
    //public static final String URL = "jdbc:mysql://192.168.3.22:3306/information_schema?useUnicode=true"; // 数据库连接的url

    public static final String URL = "jdbc:opengauss://192.168.5.86:15400/k12_brain_tenant1_dev?currentSchema=public&batchMode=off";
    public static final String USERNAME = "newmanager"; // 数据库用户名
    public static final String PASSWORD = "opengauss@k12brain"; // 数据库密码
    //public static final String SCHEMA = "public"; // 数据库名

    public static Connection getMySQLConnection() throws Exception {
        // 1.mysql加载驱动程序
       // Class.forName("com.mysql.cj.jdbc.Driver");
        //opengauss加载驱动
        Class.forName("org.opengauss.Driver");
        // 2.获得数据库链接
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    /**
     * 查数据库中的所有的表
     */
    public static List<String> getTables(String schema) throws Exception {
        List<String> tables = new ArrayList<>();
        Connection conn = getMySQLConnection();
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(
                "SELECT TABLE_NAME FROM information_schema.`TABLES`  WHERE TABLE_SCHEMA = '" + schema + "'");
        while (rs.next()) {
            tables.add(rs.getString("TABLE_NAME"));
        }
        release(conn, st, rs);
        return tables;
    }

    /**
     * 查数据库中的表中的所有字段
     */
    public static List<Column> getColumns(String schema, String tableName) throws Exception {
        List<Column> columns = new ArrayList<>();
        Connection conn = getMySQLConnection();
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(
                "select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLUMN_COMMENT from INFORMATION_SCHEMA.Columns where table_name='"
                        + tableName + "' and table_schema='" + schema + "'");
        while (rs.next()) {
            Column clu = new Column();
            clu.setColumnName(rs.getString("COLUMN_NAME")); // 列名
            clu.setDataType(rs.getString("DATA_TYPE")); // 列数据类型
            clu.setColumnComment(rs.getString("COLUMN_COMMENT")); // 注释
            clu.setCharacterMaximumLength(rs.getString("CHARACTER_MAXIMUM_LENGTH")); // 字段长度
            columns.add(clu);
        }
        release(conn, st, rs);
        return columns;
    }

    /**
     * 释放资源
     */
    private static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {
        rs.close();
        st.close();
        conn.close();
    }


}
